package gmusic.mb.dao.impl;

import gmusic.mb.bo.ArtistMBBean;
import gmusic.mb.bo.ReleaseMBBean;
import gmusic.mb.bo.TrackMBBean;
import gmusic.mb.dao.MusicBrainzDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import algutil.connexion.GestionConnexion;

public class MusicBrainzDAOImpl implements MusicBrainzDAO {

	private static final Logger log = Logger
			.getLogger(MusicBrainzDAOImpl.class);

	private static MusicBrainzDAO instance = null;

	private MusicBrainzDAOImpl() {

	}

	public static MusicBrainzDAO getInstance() {
		if (instance == null) {
			instance = new MusicBrainzDAOImpl();
		}
		return instance;
	}

	public List<ArtistMBBean> getAllArtists() throws SQLException {
		List<ArtistMBBean> artistes = new ArrayList<ArtistMBBean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM mb_artist ORDER BY nom");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			artistes.add(new ArtistMBBean(r));
		}
		r.close();
		p.close();
		return artistes;
	}

	public ArtistMBBean getArtist(String ambid) throws SQLException {
		ArtistMBBean artiste = null;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM mb_artist WHERE mbid=?");
		p.setString(1, ambid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			artiste = new ArtistMBBean(r);
		}
		r.close();
		p.close();
		return artiste;
	}

	public int getNbArtists() throws SQLException {

		int nbArtists = -1;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT count(*) FROM mb_artist");
		ResultSet r = p.executeQuery();

		if (r.next()) {
			nbArtists = r.getInt(1);
		}

		r.close();
		p.close();

		return nbArtists;
	}

	public int getNbReleases() throws SQLException {

		int nbReleases = -1;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT count(*) FROM mb_release");
		ResultSet r = p.executeQuery();

		if (r.next()) {
			nbReleases = r.getInt(1);
		}

		r.close();
		p.close();

		return nbReleases;
	}

	public int getNbTracks() throws SQLException {

		int nbtracks = -1;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT count(*) FROM mb_track");
		ResultSet r = p.executeQuery();

		if (r.next()) {
			nbtracks = r.getInt(1);
		}

		r.close();
		p.close();

		return nbtracks;
	}

	public List<String> getPremieresLettreArtistes() throws SQLException {
		List<String> premL = new ArrayList<String>();

		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT DISTINCT SUBSTRING( nom, 1, 1 ) lettre FROM mb_artist");
		ResultSet r = p.executeQuery();

		while (r.next()) {
			premL.add(r.getString("lettre"));
		}

		r.close();
		p.close();

		return premL;
	}

	public ReleaseMBBean getRelease(String rmbid) throws SQLException {
		ReleaseMBBean rb = null;
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM mb_release WHERE mbid=?");
		p.setString(1, rmbid);
		ResultSet r = p.executeQuery();
		if (r.next()) {
			rb = new ReleaseMBBean(r);
		} else {
			log.error("La release n'a pas ete trouve " + rmbid);
		}
		r.close();
		p.close();
		return rb;
	}

	public List<ReleaseMBBean> getReleases(String ambid) throws SQLException {

		List<ReleaseMBBean> releases = new ArrayList<ReleaseMBBean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM mb_release WHERE artist_mbid=?");
		p.setString(1, ambid);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			releases.add(new ReleaseMBBean(r));
		}
		r.close();
		p.close();
		return releases;
	}

	public List<TrackMBBean> getTracks(String rmbid) throws SQLException {
		List<TrackMBBean> tracks = new ArrayList<TrackMBBean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM mb_track WHERE release_mbid=?");
		p.setString(1, rmbid);
		ResultSet r = p.executeQuery();
		while (r.next()) {
			tracks.add(new TrackMBBean(r));
		}

		if (tracks.size() == 0) {
			log.warn("La release n'a pas de tracks " + rmbid);
		}
		r.close();
		p.close();
		return tracks;
	}

	public boolean persist(ArtistMBBean ab) throws SQLException {

		Connection c = GestionConnexion.getConnexionGMusic();
		log.info("Insersion en BD de l'artiste " + ab);
		PreparedStatement p = c
				.prepareStatement("INSERT INTO mb_artist (mbid, nom, creation_date) VALUES (?, ?, now())");
		p.setString(1, ab.getMbid());
		p.setString(2, ab.getNom());
		p.executeUpdate();
		p.close();
		return true;
	}

	public boolean persist(ReleaseMBBean rb) throws SQLException {

		Connection c = GestionConnexion.getConnexionGMusic();
		log.info("Insertion en BD de la release " + rb);
		PreparedStatement p = c
				.prepareStatement("INSERT INTO mb_release (mbid, nom, artist_mbid, annee, type_id, creation_date) VALUES (?, ?, ?, ?, ?, now())");
		log
				.debug("INSERT INTO mb_release (mbid, nom, artist_mbid, annee, type_id, creation_date) VALUES ('"
						+ rb.getMbid()
						+ "', '"
						+ rb.getNom()
						+ "', '"
						+ rb.getArtistMbid()
						+ "', "
						+ rb.getAnnee()
						+ ", "
						+ rb.getTypeId() + ", now())");
		p.setString(1, rb.getMbid());
		p.setString(2, rb.getNom());
		p.setString(3, rb.getArtistMbid());
		p.setInt(4, rb.getAnnee());
		p.setInt(5, rb.getTypeId());
		p.executeUpdate();

		p.close();
		return true;
	}

	public boolean persist(TrackMBBean tb) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		log.info("Insertion en BD de la chanson " + tb);
		PreparedStatement p = c
				.prepareStatement("INSERT INTO mb_track (mbid, release_mbid, num, nom, duree, creation_date) VALUES (?, ?, ?, ?, ?, now())");
		log
				.debug("INSERT INTO mb_track (mbid, release_mbid, num, nom, duree, creation_date) VALUES ('"
						+ tb.getMbid()
						+ "', '"
						+ tb.getReleaseMbid()
						+ "', "
						+ tb.getNumero()
						+ ", '"
						+ tb.getNom()
						+ "', "
						+ tb.getDuree() + ", now())");
		p.setString(1, tb.getMbid());
		p.setString(2, tb.getReleaseMbid());
		p.setInt(3, tb.getNumero());
		p.setString(4, tb.getNom());
		p.setInt(5, tb.getDuree());
		p.executeUpdate();
		p.close();
		return true;
	}

	public List<ArtistMBBean> rechercherArtistes(String nom)
			throws SQLException {
		List<ArtistMBBean> artistes = new ArrayList<ArtistMBBean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM mb_artist WHERE nom like ?");
		p.setString(1, "%" + nom + "%");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			artistes.add(new ArtistMBBean(r));
		}

		r.close();
		p.close();
		return artistes;
	}

	public List<ReleaseMBBean> rechercherReleases(String nom)
			throws SQLException {
		List<ReleaseMBBean> releases = new ArrayList<ReleaseMBBean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM mb_release WHERE nom like ?");
		p.setString(1, "%" + nom + "%");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			releases.add(new ReleaseMBBean(r));
		}
		r.close();
		p.close();

		return releases;
	}

	public List<TrackMBBean> rechercherTracks(String nom) throws SQLException {
		List<TrackMBBean> tracks = new ArrayList<TrackMBBean>();
		Connection c = GestionConnexion.getConnexionGMusic();
		PreparedStatement p = c
				.prepareStatement("SELECT * FROM mb_track WHERE nom like ?");
		p.setString(1, "%" + nom + "%");
		ResultSet r = p.executeQuery();
		while (r.next()) {
			tracks.add(new TrackMBBean(r));
		}
		r.close();
		p.close();

		return tracks;
	}

	public void supprimerArtiste(String ambid) throws SQLException {
		Connection c = GestionConnexion.getConnexionGMusic();
		// TODO supprimer titres et releases
		PreparedStatement p = c
				.prepareStatement("DELETE FROM mb_artist WHERE mbid=?");
		p.setString(1, ambid);
		p.executeUpdate();
		p.close();
	}

}
